﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Reflection;
using System.ComponentModel;
using System.Globalization;
using MillerApp.CommonLib.Utility;

namespace MillerApp.CommonLib.OfficeProvider
{
    /// <summary>
    /// Excel操作服务类
    /// </summary>
    public class ExcelProvider : IOfficeProvider
    {
        private string _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\"";
        /// <summary>
        /// 连接字符串  {0}为Excel文件路径
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        public DataSet GetDataFormExcel(string excelFilePath,string sheetName)
        {
            DataSet ds = new DataSet();
            DbConnection conn = this.GetConnectionToExcel(excelFilePath);
            string sql = "select * from [" + sheetName + "]";
            conn.Open();
            DbDataAdapter myCommand = new OleDbDataAdapter(sql, conn as OleDbConnection);
            myCommand.Fill(ds, sheetName);
            conn.Close();
            return ds;
        }

        public DataSet GetDataFormExcel(string excelFilePath)
        {
            DataSet ds = new DataSet();
            string sheet1Name = "Sheet1$";
            List<string> sheetNames = GetExcelSheetNames(excelFilePath);
            if (sheetNames == null || sheetNames.Count <= 0)
            {
                return null;
            }
            sheet1Name = sheetNames[0];
            ds = GetDataFormExcel(excelFilePath, sheet1Name);
            return ds;
        }

        public List<string> GetExcelSheetNames(string excelFilePath)
        {
            DbConnection xlsConn = GetConnectionToExcel(excelFilePath);
            if (xlsConn.State != ConnectionState.Open)
            {
                xlsConn.Open();
            }
            List<string> sheetNameCollection = new List<string>();
            DataTable dtOle = xlsConn.GetSchema("Tables");
            DataTableReader dtReader = new DataTableReader(dtOle);
            while (dtReader.Read())
            {
                sheetNameCollection.Add(dtReader["Table_Name"].ToString());
            }
            dtReader = null;
            dtOle = null;
            xlsConn.Close();
            xlsConn.Dispose();
            return sheetNameCollection;
        }

        private DbConnection GetConnectionToExcel(string filePath)
        {
            string tempConnectionString = string.Format(this.ConnectionString, filePath);
            OleDbConnection conn = new OleDbConnection(tempConnectionString);
            return conn;
        }

    }
}
